/**
 * 
 */
package com.orange.modules.dao.expert;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.dbhelp.page.Page;
import com.orange.modules.entity.expert.AuthExam;
import com.orange.modules.entity.expert.AuthGroup;

/**
 * @author gaogs
 *
 * 2016-11-30
 */
@Repository
public class AuthExamRepository {
	
	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	@Autowired
	private AuthExamDao authExamDao;
	
	
	/**
	 * 保存试题
	 * @param a
	 */
	public void saveAuthExamInfo(AuthExam a){
		authExamDao.save(a);
	}
	
	/**
	 * 删除试题
	 * @param pid
	 */
	public void deleteAuthExamInfo(String pid){
		authExamDao.delete(pid);
	}
	

	/**
	 * 根据id 查询试题信息
	 * @param pid
	 * @return
	 */
	public AuthExam queryAuthExamInfo(String pid){
		AuthExam a =authExamDao.findOne(pid);
		return a;
	}
	
	/**
	 * 根据名称查询试题信息
	 * @param name
	 * @return
	 * @throws SQLException
	 */
	public AuthExam queryAuthExamInfoByName(String name) throws SQLException{
		String sql="SELECT aa.p_id as pid ,aa.s_name as name ,aa.s_task_id as taskId ,aa.s_subject as subject ," +
				" aa.s_file_name as fileName ,aa.s_file_url as fileurl ,aa.s_create_time as createTime ," +
				" aa.s_number as number ,aa.s_major as major " +
				" FROM t_auth_exam  as aa  WHERE aa.s_name=?";
		AuthExam a=dbHelper.getBean(sql, AuthExam.class, name)	;	
		return a;
	}
	
	
	/**
	 * 分页查询试题
	 * @param map
	 * @return
	 * @throws SQLException
	 */
	public Page<AuthExam> queryAuthExamPage(Map<String, String> map) throws SQLException {
		StringBuilder sql = new StringBuilder("SELECT TOP 1000 aa.p_id as pid ,aa.s_name as name ,aa.s_task_id as taskId ,aa.s_subject as subject ," +
				" aa.s_file_name as fileName ,aa.s_file_url as fileurl ,aa.s_create_time as createTime ," +
				" aa.s_number as number ,bb.s_name as taskName ,aa.s_major as major, c.s_name as subjectName" +
				" FROM t_auth_exam as aa" +
				" LEFT JOIN t_auth_special_task as bb ON aa.s_task_id=bb.p_id" +
				" left join t_auth_subject as c on aa.s_subject = c.p_id" +
				" WHERE 1=1 ");
		if (StringUtils.isNotBlank(map.get("major"))) {
			sql.append(" AND aa.s_major= '" + map.get("major")+ "'");
		}
		if (StringUtils.isNotBlank(map.get("taskId"))) {
			sql.append(" AND aa.s_task_id ='" + map.get("taskId")+ "'");
		}
		if (StringUtils.isNotBlank(map.get("subject"))) {
			sql.append(" AND aa.s_subject ='" + map.get("subject")+ "'");
		}
		sql.append("  ORDER BY aa.s_major,aa.s_task_id, aa.s_number ASC ");
		Page<AuthExam> authExam = dbHelper.getPage(sql.toString(),AuthExam.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return authExam;
	}
	
	/**
	 * 根据专业技能及科目查询所有试题
	 * @param taskId
	 * @param subjectId
	 * @return
	 * @throws SQLException 
	 */
	public List<AuthExam> queryAuthExamListByTask(String taskId,String major ,String subject) throws SQLException{
		String sql="SELECT TOP 1000 aa.p_id as pid ,aa.s_name as name ,aa.s_task_id as taskId ,aa.s_subject as subject ," +
				" aa.s_file_name as fileName ,aa.s_file_url as fileurl ,aa.s_create_time as createTime ," +
				" aa.s_number as number ,aa.s_major as major" +
				" FROM t_auth_exam as aa WHERE aa.s_task_id=? AND aa.s_major=?";
		if (StringUtils.isNotBlank(subject)) {
			sql+=" AND aa.s_subject ='" + subject+ "'";
		}
		sql+=" ORDER BY aa.s_number ASC ";
		List<AuthExam>  ls=dbHelper.getBeanList(sql, AuthExam.class, taskId ,major);
		return ls;
	}
	
	
	
	
	
	
	
}
